package action;

import dao.ConnectorFactory;
import dao.ExcelDao;
import model.Catalog;
import model.Column;
import model.Table;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016/6/10.
 */
public class Main {
    public static void main(String[] args){
        Connection conn = null;
        try {
            conn = ConnectorFactory.getMysqlConnection();
            List<Catalog> catalogs = getMetaData(conn);
            writeInExcel(catalogs,"e://test");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接器获取异常");
        }
        System.out.println("good");

        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Catalog> getMetaData(Connection conn) throws SQLException {
        DatabaseMetaData dbmd =  conn.getMetaData();
        ResultSet catalogsRS =  dbmd.getCatalogs();
        List<Catalog> catalogs = new ArrayList<Catalog>();
        while (catalogsRS.next()){
            //database名
            String catalog = catalogsRS.getString(1);
            System.out.println(catalog);
            /**
             * 获取给定类别中使用的表的描述。
             * 方法原型:ResultSet getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types);
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * tableNamePattern - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             * types - 表类型数组; "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM";null表示包含所有的表类型;可包含单字符通配符("_"),或多字符通配符("%");
             */
            Catalog catalogmodel = new Catalog();
            List<Table> tables = new ArrayList<Table>();
            catalogmodel.setTables(tables);
            catalogmodel.setCatalogName(catalog);
            catalogs.add(catalogmodel);

            ResultSet tablesRS =  dbmd.getTables(catalog,null,"%",new String[]{"TABLE"});
            while (tablesRS.next()){
                ResultSetMetaData rsmd = tablesRS.getMetaData();
                int colcount = rsmd.getColumnCount();
                Table table = new Table();
                List<Column> columns = new ArrayList<Column>();

                table.setColumns(columns);
                table.setTableName(tablesRS.getString("TABLE_NAME"));
                table.setRemark(tablesRS.getString("REMARKS"));
                table.setType(tablesRS.getString("TABLE_TYPE"));
                tables.add(table);

                System.out.println("表名： "+tablesRS.getString("TABLE_NAME"));
                System.out.println("表类型： "+tablesRS.getString("TABLE_TYPE"));
                System.out.println("表备注： "+tablesRS.getString("REMARKS"));



                //获取表中的所有的列
                ResultSet colRS =  dbmd.getColumns(catalog,null,tablesRS.getString("TABLE_NAME"),"%");
                System.out.print("列名： ");
                while (colRS.next()){
                    Column column = new Column();
                    column.setRemark(colRS.getString("REMARKS"));
                    column.setColumnName(colRS.getString("COLUMN_NAME"));
                    column.setColumnSize(colRS.getInt("COLUMN_SIZE"));
                    column.setColumnType(colRS.getString("TYPE_NAME"));
                    column.setDefultValue(colRS.getString("COLUMN_DEF"));
                    int isnullable = colRS.getInt("NULLABLE");

                    column.setIsNullable(isnullable ==0?
                            Column.NULLINFO.NOTNULLABLE:Column.NULLINFO.NULLABLE);

                    columns.add(column);

                    System.out.print(colRS.getString("COLUMN_NAME")+" ");
                    System.out.print(colRS.getString("TYPE_NAME")+" ");
                    System.out.print(colRS.getString("REMARKS")+" ");
                    System.out.print(colRS.getString("COLUMN_DEF")+" "); //默认值
                    /**
                     *  0 (columnNoNulls) - 该列不允许为空
                     *  1 (columnNullable) - 该列允许为空
                     *  2 (columnNullableUnknown) - 不确定该列是否为空
                     */
                    System.out.print(colRS.getInt("NULLABLE")+" ");
                    System.out.print(colRS.getInt("COLUMN_SIZE")+" "); //列大小
                }
                System.out.println();
            }
        }
        return catalogs;
    }

    public static void writeInExcel(List<Catalog> catalogs,String path){

        for (Catalog catalog:catalogs){
            HSSFWorkbook wb = ExcelDao.createHSSFWorkBook();
            String wbPath = path +"/" +catalog.getCatalogName()+".xls";
            for (Table t : catalog.getTables()){
                HSSFSheet sheet = ExcelDao.createSheet(wb,("".equals(t.getRemark().trim())||t.getRemark() == null)?t.getTableName():t.getRemark());
                ExcelDao.addContentInCell("表名",sheet,null,0,0,null);
                ExcelDao.addContentInCell(t.getTableName(),sheet,null,0,1,null);
                ExcelDao.addContentInCell("备注",sheet,null,1,0,null);
                ExcelDao.addContentInCell(t.getRemark(),sheet,null,1,1,null);
                ExcelDao.addContentInCell("表类型",sheet,null,2,0,null);
                ExcelDao.addContentInCell(t.getType(),sheet,null,2,1,null);

                ExcelDao.addContentInCell("列名",sheet,null,3,0,null);
                ExcelDao.addContentInCell("类型",sheet,null,3,1,null);
                ExcelDao.addContentInCell("默认值",sheet,null,3,2,null);
                ExcelDao.addContentInCell("是否可以为空",sheet,null,3,3,null);
                ExcelDao.addContentInCell("备注",sheet,null,3,4,null);

                int rowcount = 4;
                for (Column c : t.getColumns()){
                    ExcelDao.addContentInCell(c.getColumnName(),sheet,null,rowcount,0,null);
                    ExcelDao.addContentInCell(c.getColumnType()+"("+c.getColumnSize()+")",sheet,null,rowcount,1,null);
                    ExcelDao.addContentInCell(c.getDefultValue(),sheet,null,rowcount,2,null);
                    ExcelDao.addContentInCell(c.getIsNullable() == Column.NULLINFO.NULLABLE?"Y":"N",sheet,null,rowcount,3,null);
                    ExcelDao.addContentInCell(c.getRemark(),sheet,null,rowcount,4,null);
                    rowcount++;
                }

            }
            ExcelDao.saveHSSFWorkBook(wb,wbPath);

        }
    }


}
